import pymysql
from pymysql.cursors import DictCursor

"""
    MysqlConn:Mysql驱动连接
        auto_commit:自动提交
    
        select:默认sql查询函数
        update:默认sql修改函数
        do_commit:提交
        do_close:结束函数
        
        select_wsw:从哪个表查询什么内容
        select_wsw_w:从哪个表查询什么内容指定内容
        insert_data_many:插入哪个表什么数据(多条，二维数组)
        insert_data:插入哪个表什么数据
        change_data:修改哪个表什么数据
        clear_table:清除表格
        delete_data:删除哪个表什么数据
"""


class MysqlConn(object):
    def __init__(self, host, db, user, password, port=3306, charset="utf8", cursor_dict=None):
        self.conn = pymysql.connect(host=host, db=db, user=user, password=password, port=port, charset=charset)

        cursor_dict = DictCursor if cursor_dict else None
        self.cur = self.conn.cursor(cursor_dict)

    def __enter__(self):
        return self

    def __exit__(self, my_type, value, trace):
        self.do_close()

    def select(self, sql, content_list=None):
        if content_list:
            self.cur.execute(sql, content_list)
        else:
            self.cur.execute(sql)
        return self.cur.fetchall()

    def select_wsw(self, which, what="*"):
        sql = "select {} from {}".format(what, which)
        return self.select(sql)

    def select_wsw_w(self, which, what, where):
        sql = "select {} from {}".format(what, which)
        where_sql = "where "
        content_list = []
        tmp_count = 1
        for i in where:
            if tmp_count > 1:
                where_sql += "and "
            content_list.append(where.get(i))
            where_sql += i
            where_sql += "%s "
            tmp_count += 1
        return self.select(sql + " " + where_sql, content_list)

    def update(self, sql, content_list=None):
        try:
            if content_list:
                affect = self.cur.execute(sql, content_list)
            else:
                affect = self.cur.execute(sql)
        except Exception as e:
            print(e)
            self.conn.rollback()
            return 0, 0
        else:
            self.conn.commit()

        return affect, self.cur.lastrowid

    def insert_data_many(self, sql, content_list):
        try:
            affect = self.cur.executemany(sql, content_list)
            self.conn.commit()
            return affect
        except Exception as e:
            print(e)
            self.conn.rollback()
            return 0

    def insert_data(self, which, what, field=(), many=False):
        if len(field) == 0:
            field = ""
        sql = "insert into "
        sql += which
        if len(field) != 0:
            sql += "(" + ",".join(field) + ")"
        sql += " values"
        if many:
            sql += str(["%s"] * len(what)).replace("'", "").replace("[", "(").replace("]", ")")
            return self.insert_data_many(sql, what)
        sql += str(["%s"] * len(what)).replace("'", "").replace("[", "(").replace("]", ")")
        return self.update(sql, what)

    def change_data(self, which, change_dict, where=None):
        content_list = []
        sql = "update "
        sql += which
        sql += " set "
        change_list = []
        for i in change_dict:
            change_list.append(i + "=" + "%s")
        sql += ",".join(change_list)
        content_list.extend(change_dict.values())
        if where:
            where_list = []
            for k in where:
                where_list.append(k + "%s")
            sql += " where " + " and ".join(where_list)
            content_list.extend(tuple(where.values()))
        return self.update(sql, content_list)

    def delete_data(self, table, where=None):
        sql = "delete from {}".format(table)
        if not where:
            return
        where_list = []
        for k in where:
            where_list.append(k + "%s")
        sql += " where " + " and ".join(where_list)
        return self.update(sql, tuple(where.values()))

    def clear_table(self, table):
        return self.update("delete from {}".format(table))

    def fast_clear_table(self, table):
        self.update("set foreign_key_checks=0")
        self.update("truncate table {}".format(table))
        self.update("set foreign_key_checks=1")

    def do_close(self):
        self.cur.close()
        self.conn.close()


if __name__ == "__main__":
    m = MysqlConn(host="192.168.2.3", db="super_card", user="root", password="111111")
    table_list = ["sc_detail", "sc_product", "sc_style", "sc_tmpl", "sc_user"]
    for i in table_list:
        m.fast_clear_table(i)
    m.do_close()
